package lab03.test12;

import java.sql.*;

public class Test12 {
    public static void main(String[] args) {

//        3+1:conection,statement,resultset + 1个sql语句
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String sql1 = "select * from t_users";
        String sql2 = "select * from t_users where username = 'admin'";
        String sql3 = "select * from t_users where username = 'admin' and password = '123456'";
        String sql4 = "select * from t_users where regsiter > 10000000";

        try {
//            加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

//            连接数据库
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC&characterEncoding=utf-8","" +
                            "root","123456");

//            statement声明一个语句对象，通过连接数据库对象connection
//            调用createStatement()方法创建SQL语句
            statement = connection.createStatement();

            resultSet = statement.executeQuery(sql1);
            while (resultSet.next()){
                System.out.print(resultSet.getInt(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.print(resultSet.getLong(4));
                System.out.println();
            }
            System.out.println("------------------------------");

            resultSet = statement.executeQuery(sql2);
            while (resultSet.next()){
                System.out.print(resultSet.getInt(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.print(resultSet.getLong(4));
                System.out.println();
            }
            System.out.println("------------------------------");

            resultSet = statement.executeQuery(sql3);
            while (resultSet.next()){
                System.out.print(resultSet.getInt(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.print(resultSet.getLong(4));
                System.out.println();
            }
            System.out.println("------------------------------");

            resultSet = statement.executeQuery(sql4);
            while (resultSet.next()){
                System.out.print(resultSet.getInt(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.print(resultSet.getLong(4));
                System.out.println();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                statement.close();
                connection.close();
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}
